﻿using System.Data.SqlClient;
using System;
using System.Web;
using System.Configuration;
using System.Data;

/// <summary>
///SqlHelper 的摘要说明
/// </summary>
namespace RED_NET_Core.Helper
{
    public class SqlHelper
    {
        // private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["Connectionstring"].ConnectionString;
        private SqlConnection myconnection = null;

        public SqlHelper()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
            myconnection = new SqlConnection(Utilty.Utility.connStr);
            if (myconnection.State == ConnectionState.Closed)
            {
                try
                {
                    myconnection.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
            }
        }
        ~SqlHelper()
        {

        }
        public SqlCommand GetCommand(string CmdText, params SqlParameter[] MyPara)
        {
            SqlCommand mycommand = new SqlCommand();
            mycommand.Connection = myconnection;
            mycommand.CommandText = CmdText;
            mycommand.CommandType = CommandType.Text;
            if (MyPara != null)
            {
                foreach (SqlParameter para in MyPara)
                {
                    mycommand.Parameters.Add(para);
                }
            }

            return mycommand;
        }
        public void Close()
        {
            myconnection.Close();
        }

        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(Utilty.Utility.connStr);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
        }

        public static DataSet Query(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(Utilty.Utility.connStr))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch(System .Data .SqlClient .SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(Utilty.Utility.connStr))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }
    }
}